package com.Jiang.dao.User;
import com.Jiang.dao.DateBase;
import com.Jiang.pojo.User;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.Vector;

public class UserDaoImplements implements UserDao{
//    public static void main(String[] args) throws SQLException {
//        User u=new UserDaoImplements().getLoginUser(DateBase.getConnection(),"admin");
//        System.out.println(u.getUserPassword());
//    }
    @Override
    public User getLoginUser(Connection connection, String userCode) throws SQLException {
        User user=null;
        String sql="select * from smbms_user where userCode=?"; //查询语句
        Vector u=new Vector();      //容器
        u.add(userCode);
        ResultSet r=DateBase.FindDateBase(connection,sql,u.toArray()); //调用查询方法
        if(r.next()){
            user=new User();
            user.setId(r.getInt("id"));
            user.setUserCode(r.getString("userCode"));
            user.setUserName(r.getString("userName"));
            user.setUserPassword(r.getString("userPassword"));
            user.setGender(r.getInt("gender"));
            user.setBirthday(r.getDate("birthday"));
            user.setPhone(r.getString("phone"));
            user.setAddress(r.getString("address"));
            user.setUserRole(r.getInt("userRole"));
            user.setCreatedBy(r.getInt("createdBy"));
            user.setCreationDate(r.getDate("creationDate"));
            user.setModifyby(r.getInt("modifyBy"));
            user.setModifyDate(r.getDate("modifyDate"));
        }
       // r.close();
        return user;
    }

    @Override
    public int ModifyPassword(Connection connection, Integer id, String userPassword) throws SQLException {
        String sql="update smbms_user set userPassword=? where id = ?";
        Vector v=new Vector();
        v.add(userPassword);
        v.add(id);
        return DateBase.UpdateDateBase(connection,sql,v.toArray());
    }

    @Override
    public int getUserCount(Connection connection, String userName, int userRole) throws SQLException {
        String sql="select count(1) as count from smbms_user,smbms_role where smbms_user.userRole=smbms_role.id";
        Vector list=new Vector();
        if(userName!=null&&userName.length()!=0){
            sql+=" and smbms_user.userName like ?";
            list.add("%"+userName+"%");
        }
        if(userRole>0){
            sql+=" and smbms_role.id=?";
            list.add(userRole);
        }
      //  System.out.println(sql);

        ResultSet resultSet = DateBase.FindDateBase(connection, sql, list.toArray());
        if(resultSet.next())
            return resultSet.getInt("count");
        return 0;
    }

    @Override
    public Vector<User> getUserList(Connection connection, String userName, int userRole,int currentPageNo,int pageSize) throws SQLException {
        String sql="select smbms_user.*,smbms_role.roleName from smbms_user,smbms_role where smbms_user.userRole=smbms_role.id";
        Vector list=new Vector();
        if(userName!=null&&userName.length()!=0){
            sql+=" and smbms_user.userName like ?";
            list.add("%"+userName+"%");
        }
        if(userRole>0){
            sql+=" and smbms_role.id=?";
            list.add(userRole);
        }
        sql+=" order by creationDate DESC limit ?,?";
      //  System.out.println(sql);
        currentPageNo=(currentPageNo-1)*pageSize;
        list.add(currentPageNo);
        list.add(pageSize);
        ResultSet resultSet = DateBase.FindDateBase(connection, sql, list.toArray());
        Vector<User> users=new Vector<User>();
        while (resultSet.next()){

        User user=new User();
        user.setId(resultSet.getInt("id"));
        user.setUserCode(resultSet.getString("userCode"));
        user.setUserName(resultSet.getString("userName"));
        user.setGender(resultSet.getInt("gender"));
        user.setBirthday(resultSet.getDate("birthday"));
        user.setPhone(resultSet.getString("phone"));
        user.setUserRoleName(resultSet.getString("roleName"));
        users.add(user);
        }
        connection.close();
        return users;
    }

    @Override
    public int addUser(Connection connection, User user) throws SQLException {
        String sql="insert into smbms_user values (?,?,?,?,?,?,?,?,?,?,?,?,?)";
        Vector v=new Vector();
        v.add(user.getId());
        v.add(user.getUserCode());
        v.add(user.getUserName());
        v.add(user.getUserPassword());
        v.add(user.getGender());
        v.add(user.getBirthday());
        v.add(user.getPhone());
        v.add(user.getAddress());
        v.add(user.getUserRole());
        v.add(user.getCreatedBy());
        v.add(user.getCreationDate());
        v.add(user.getModifyby());
        v.add(user.getModifyDate());
        return DateBase.UpdateDateBase(connection,sql,v.toArray());
    }

    @Override
    public User getUserById(Connection connection, int Id) throws SQLException {
        String sql="select smbms_user.*,smbms_role.roleName from smbms_user,smbms_role where smbms_user.userRole=smbms_role.id and smbms_user.id=?";
        ResultSet resultSet = DateBase.FindDateBase(connection, sql, new Object[]{Id});
        User user=null;
        if(resultSet.next()){
            user=new User();
            user.setId(resultSet.getInt("id"));
            user.setUserCode(resultSet.getString("userCode"));
            user.setUserName(resultSet.getString("userName"));
            user.setGender(resultSet.getInt("gender"));
            user.setBirthday(resultSet.getDate("birthday"));
            user.setPhone(resultSet.getString("phone"));
            user.setUserRoleName(resultSet.getString("roleName"));
            user.setAddress(resultSet.getString("address"));
        }
        return user;
    }

    @Override
    public int modifyUser(Connection connection, User user,int modifyBy) throws SQLException {
        String sql="update smbms_user\n" +
                "set  userName=?,gender=?,birthday=?,phone=?,address=?,userRole=?,modifyBy=?,modifyDate=?\n" +
                "where id=?;";
        Vector v=new Vector();
        v.add(user.getUserName());
        v.add(user.getGender());
        v.add(user.getBirthday());
        v.add(user.getPhone());
        v.add(user.getAddress());
        v.add(user.getUserRole());
        v.add(modifyBy);
        v.add(new Date());
        v.add(user.getId());
        return DateBase.UpdateDateBase(connection,sql,v.toArray());
    }

    @Override
    public int deluser(Connection connection, int Id) throws SQLException {
        String sql="delete from smbms_user where id=?";
        return DateBase.UpdateDateBase(connection,sql,new Object[]{Id});
    }


}
